package com.shujia.tour

import com.shujia.util.SparkTool
import org.apache.spark.sql.SparkSession

object DalTourProvinceTouristWideApp extends SparkTool{
  /**
    * 在子类中实现run方法，实现自定义的代码逻辑
    *
    * 在子类run方法的前面加上下面两行代码
    * import spark.implicits._
    * import org.apache.spark.sql.functions._
    *
    * @param spark spark的环境
    */
  override def run(spark: SparkSession): Unit = {
    spark.sql( s"""
                  |
         |INSERT OVERWRITE table  dal_tour.dal_tour_province_wide_msk_d partition(day_id=$day_id)
                  |select
                  |/*+ BROADCAST(f) */
                  |e.mdn,
                  |e.prov_name as d_province_name,
                  |f.city_name as o_city_name,
                  |f.prov_name as o_province_name,
                  |e.number_attr as number_attr,
                  |e.d_distance_section as d_distance_section,
                  |e.d_stay_time as d_stay_time,
                  |e.gender as gender,
                  |e.trmnl_brand as trmnl_brand,
                  |e.packg as pckg_price,
                  |e.conpot as conpot,
                  |e.age as age
                  |from
                  |(select /*+ BROADCAST(d) */ c.*,d.prov_name from
                  |(select
                  |a.mdn,
                  |a.d_province_id,
                  |a.source_county_id,
                  |b.number_attr,
                  |case
                  |when a.d_max_distance > 10 and a.d_max_distance< 50 then '10-50'
                  |when a.d_max_distance > 50 and a.d_max_distance< 80 then '50-80'
                  |when a.d_max_distance > 80 and a.d_max_distance< 120 then '80-120'
                  |when a.d_max_distance > 120 and a.d_max_distance< 200 then '120-200'
                  |when a.d_max_distance > 200 and a.d_max_distance< 400 then '200-400'
                  |when a.d_max_distance > 400 and a.d_max_distance< 800 then '400-800'
                  |else '800-~' end as d_distance_section,
                  |case
                  |when a.d_stay_time/60>3 and a.d_stay_time/60<5 then "3-5"
                  |when a.d_stay_time/60>5 and a.d_stay_time/60<8 then "5-8"
                  |when a.d_stay_time/60>8 and a.d_stay_time/60<12 then "8-12"
                  |when a.d_stay_time/60>12 and a.d_stay_time/60<20 then "12-20"
                  |else "20~" end as d_stay_time,
                  |case
                  |when b.gender = 1 then '男'
                  |when b.gender =2 then '女'
                  |else '其它' end gender,
                  |b.trmnl_brand,
                  |b.packg,
                  |b.conpot,
                  |b.age
                  |from
                  |(select * from  dal_tour.dal_tour_province_tourist_msk_d where day_id=$day_id ) as a
                  |join
                  |(select * from dim.dim_usertag_msk_m where month_id=$month_id) as b
                  |on a.mdn=b.mdn) as c
                  |join
                  |(select distinct  prov_id,prov_name  from dim.dim_admincode ) as d
                  |on c.d_province_id=d.prov_id
                  |) as e
                  |join dim.dim_admincode as f
                  |on e.source_county_id = f.county_id
                  |
        |
      """.stripMargin)
  }
}
